//
//  InformationViewController.m
//  DianNaoWang
//
//  Created by dllo on 15/11/2.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseManager.h"
static sqlite3 *dbPoint = nil;
@implementation DataBaseManager
+ (void)openDataBase
{
    if (dbPoint) {
        return;
    }
    NSString *Path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    Path = [Path stringByAppendingPathComponent:@"CFITLoverDataBase.sqlite"];
    sqlite3_open(Path.UTF8String, &dbPoint);
    ////NSLog(@"%@", Path);

}
+ (void)closeDataBase
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        dbPoint = nil;
    }else{
        //NSLog(@"关闭失败");
    }
}

#pragma mark - 收藏的
+ (void)creatTable
{
    [self openDataBase];//**************************key一定要number吗???????答:不一定AUTOINCREMENT表示自增
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS ITLoverWapUrlString(number INTEGER PRIMARY KEY AUTOINCREMENT, WapUrlString varchar(500), title varchar(200))";
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建成功");
    }else{
        //NSLog(@"创建失败%d",result);
    }
    [self closeDataBase];
}
+ (void)insertFavoriteWapByFavoriteWapPage:(FavoriteWapPage *)favoriteWap

{
    //打开数据库
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO ITLoverWapUrlString(WapUrlString, title) VALUES('%@', '%@')",favoriteWap.wapUrlString, favoriteWap.title];
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"插入成功");
    }else{
        //NSLog(@"插入失败: %d", result);
    }
    [self closeDataBase];
}
+ (void)deleteFavoriteWapByFavoriteWapPage:(FavoriteWapPage *)favoriteWap
{
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM ITLoverWapUrlString WHERE title = '%@'", favoriteWap.title];
    //执行
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else{
        //NSLog(@"删除失败:%d", result);
    }
    //关闭
    [self closeDataBase];
}
+ (NSMutableArray *)selectWap
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = @"SELECT * FROM ITLoverWapUrlString";
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *wapUrlString = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            NSString *FwapUrlString = [NSString stringWithUTF8String:(const char *)wapUrlString];
            NSString *FTitle = [NSString stringWithUTF8String:(const char *)title];
            FavoriteWapPage *eve = [[FavoriteWapPage alloc]init];
            eve.wapUrlString = FwapUrlString;
            eve.title = FTitle;
            [array addObject:eve];
        }
    }else{
        //NSLog(@"查询失败%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;

}

+ (NSMutableArray *)selectAllFavoriteByWapTitle:(NSString *)title
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = [NSString stringWithFormat:@"SELECT * FROM ITLoverWapUrlString WHERE title LIKE '%%%@%%'",title];
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *wapUrlString = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            NSString *FwapUrlString = [NSString stringWithUTF8String:(const char *)wapUrlString];
            NSString *FTitle = [NSString stringWithUTF8String:(const char *)title];
            FavoriteWapPage *eve = [[FavoriteWapPage alloc]init];
            eve.wapUrlString = FwapUrlString;
            eve.title = FTitle;
            [array addObject:eve];
        }
    }else{
        //NSLog(@"查询失败%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;
    
}

#pragma mark - 最近浏览的
+ (void)creatPriceTable
{
    [self openDataBase];//**************************key一定要number吗???????不一定AUTOINCREMENT表示自增
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS ITLoverPriceUrlString(number INTEGER PRIMARY KEY AUTOINCREMENT, WapUrlString varchar(500), title varchar(200))";
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建成功");
    }else{
        //NSLog(@"创建失败%d",result);
    }
    [self closeDataBase];
}
+ (void)insertFavoritePricePage:(FavoriteWapPage *)favoriteWap
{
    //打开数据库
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO ITLoverPriceUrlString(WapUrlString, title) VALUES('%@', '%@')",favoriteWap.wapUrlString, favoriteWap.title];
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"插入成功");
    }else{
        //NSLog(@"插入失败: %d", result);
    }
    [self closeDataBase];
}

+ (void)deleteFavoritePricePage:(FavoriteWapPage *)favoriteWap
{
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM ITLoverPriceUrlString WHERE title = '%@'", favoriteWap.title];
    //执行
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else{
        //NSLog(@"删除失败:%d", result);
    }
    //关闭
    [self closeDataBase];
}

+ (NSMutableArray *)selectPrice
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = @"SELECT * FROM ITLoverPriceUrlString";
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *wapUrlString = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            NSString *FwapUrlString = [NSString stringWithUTF8String:(const char *)wapUrlString];
            NSString *FTitle = [NSString stringWithUTF8String:(const char *)title];
            FavoriteWapPage *eve = [[FavoriteWapPage alloc]init];
            eve.wapUrlString = FwapUrlString;
            eve.title = FTitle;
            [array addObject:eve];
        }
    }else{
        //NSLog(@"查询失败%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;
}


#pragma mark - 首页频道的
+ (void)creatChannelTable
{
    [self openDataBase];//**************************key一定要number吗???????不一定AUTOINCREMENT表示自增
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS UserChannel(number INTEGER PRIMARY KEY AUTOINCREMENT, Id varchar(100), name varchar(100))";
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建成功");
    }else{
        //NSLog(@"创建失败%d",result);
    }
    [self closeDataBase];
}
+ (void)insertChannel:(ChannelNum *)channel
{
    //打开数据库
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO UserChannel(Id, name) VALUES('%@', '%@')",channel.Id, channel.name];
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"插入成功");
    }else{
        //NSLog(@"插入失败: %d", result);
    }
    [self closeDataBase];
}
+ (void)deleteChannel:(ChannelNum *)channel
{
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM UserChannel WHERE name = '%@'", channel.name];
    //执行
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else{
        //NSLog(@"删除失败:%d", result);
    }
    //关闭
    [self closeDataBase];
}
+ (NSMutableArray *)selectChannel
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = @"SELECT * FROM UserChannel";
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *Id = sqlite3_column_text(stmt, 1);
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            NSString *FId = [NSString stringWithUTF8String:(const char *)Id];
            NSString *Fname = [NSString stringWithUTF8String:(const char *)name];
            ChannelNum *eve = [[ChannelNum alloc]init];
            eve.Id = FId;
            eve.name = Fname;
            [array addObject:eve];
        }
    }else{
        //NSLog(@"查询失败%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;
}

#pragma mark - 存放用户设置的首页频道(删除的)

+ (void)creatDChannelTable
{
    [self openDataBase];//**************************key一定要number吗???????不一定AUTOINCREMENT表示自增
    NSString *sqlString = @"CREATE TABLE IF NOT EXISTS UserDChannel(number INTEGER PRIMARY KEY AUTOINCREMENT, Id varchar(100), name varchar(100))";
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建成功");
    }else{
        //NSLog(@"创建失败%d",result);
    }
    [self closeDataBase];
}
+ (void)insertDChannel:(ChannelNum *)channel
{
    //打开数据库
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"INSERT INTO UserDChannel(Id, name) VALUES('%@', '%@')",channel.Id, channel.name];
    //执行SQL语句
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"插入成功");
    }else{
        //NSLog(@"插入失败: %d", result);
    }
    [self closeDataBase];
}
+ (void)deleteDChannel:(ChannelNum *)channel
{
    [self openDataBase];
    //编写SQL语句
    NSString *sqlString = [NSString stringWithFormat:@"DELETE FROM UserDChannel WHERE name = '%@'", channel.name];
    //执行
    int result = sqlite3_exec(dbPoint, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else{
        //NSLog(@"删除失败:%d", result);
    }
    //关闭
    [self closeDataBase];
}
+ (NSMutableArray *)selectDChannel
{
    [self openDataBase];
    sqlite3_stmt *stmt = nil;
    NSString *sqlString = @"SELECT * FROM UserDChannel";
    int result = sqlite3_prepare_v2(dbPoint, sqlString.UTF8String, -1, &stmt, nil);
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        //NSLog(@"查询成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *Id = sqlite3_column_text(stmt, 1);
            const unsigned char *name = sqlite3_column_text(stmt, 2);
            NSString *FId = [NSString stringWithUTF8String:(const char *)Id];
            NSString *Fname = [NSString stringWithUTF8String:(const char *)name];
            ChannelNum *eve = [[ChannelNum alloc]init];
            eve.Id = FId;
            eve.name = Fname;
            [array addObject:eve];
        }
    }else{
        //NSLog(@"查询失败%d", result);
    }
    sqlite3_finalize(stmt);
    [self closeDataBase];
    return array;
}



@end
